package com.example.myapplication.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.myapplication.db.domain.AccountBean;
import com.example.myapplication.db.domain.UserBean;

import java.util.ArrayList;
import java.util.List;

public class DBManager {
    private static SQLiteDatabase db;


    /* ----------------------------  数据库查询操作  ----------------------------------------------- */

    //  手机号查询用户
    public static boolean findUserByPhone(String phone){
        List<UserBean> list = DBUtils.select("SELECT * FROM user WHERE phone = '" + phone + "'", UserBean.class);
        if (list == null || list.isEmpty()){
            return false;
        }
        return true;
    }

    public static UserBean findUserByAlipayCode(String code) {
        List<UserBean> list = DBUtils.select("SELECT * FROM user WHERE alipayCode = " +
                (code == null ? "null" : "'" + code + "'"), UserBean.class);
        if (list == null || list.isEmpty()){
            return null;
        }
        return list.get(0);
    }

    //  账号密码查询用户
    public static UserBean findUserByPhoneAndPassword(String phone, String password){
        List<UserBean> list = DBUtils.select("SELECT * FROM user WHERE phone = '" + phone + "' AND password = '" + password + "'", UserBean.class);
        if (list == null || list.isEmpty()){
            return null;
        }
        return list.get(0);
    }


    //  用户ID查询账目
    public static List<AccountBean> findAccountsByUserId(int user_id){
        List<AccountBean> list = DBUtils.select("SELECT * FROM user WHERE user_id = '" + user_id + "'", AccountBean.class);
        if (list == null || list.isEmpty()){
            return null;
        }
        return list;
    }

    //  获取用户某年的账目
    public static List<AccountBean> findAccountsByYear(int user_id, int year){
        List<AccountBean> list = DBUtils.select("SELECT * FROM account WHERE user_id = " + user_id + " AND year = " + year, AccountBean.class);
        if (list == null || list.isEmpty()){
            return null;
        }
        return list;
    }

    //  获取用户某月的账目
    public static List<AccountBean> findAccountsByMonth(int user_id, int month,int year){
        List<AccountBean> list = DBUtils.select("SELECT * FROM account WHERE user_id = " + user_id +" AND year = " + year + " AND month = " + month, AccountBean.class);
        if (list == null || list.isEmpty()){
            return null;
        }
        return list;
    }


    /* ----------------------------  数据库修改操作  ----------------------------------------------- */


    //  修改用户密码
    public static boolean updatePassword(String phone, String password){
        return DBUtils.update("UPDATE user SET password = '" + password + "' WHERE phone = '" + phone + "'");
    }


    //  修改用户支付宝授权代码
    public static boolean updateAlipayCode(String phone, String code){
        return DBUtils.update("UPDATE user SET alipayCode = " +
                (code == null ? "null" : "'" + code + "'") + " WHERE phone = '" + phone + "'");
    }


    /* ----------------------------  数据库增添操作  ----------------------------------------------- */

    //  新增用户
    public static boolean insertUser(UserBean bean){
        return DBUtils.update("INSERT INTO user (phone, password, alipayCode) VALUES ('" +
                bean.getPhone() + "', '" +
                bean.getPassword() + "', " +
                (bean.getAlipayCode() == null ? "null)" : "'" + bean.getAlipayCode() + "')"));
    }


    //  新增表目
    public static boolean insertAccount(AccountBean bean){
        return DBUtils.update("INSERT INTO account (money, year, month, day, type, remark, in_out, user_id) VALUES (" +
                bean.getMoney() + ", " +
                bean.getYear() + ", " +
                bean.getMonth() + ", " +
                bean.getDay() + ", '" +
                bean.getType() + "', " +
                (bean.getRemark() == null ? "null, '" : "'" + bean.getRemark() + "', '") +
                bean.getIn_out() + "', " +
                bean.getUser_id() + ")");
    }

    public static void initDatabase(Context context){
        DBOpenHelper helper = new DBOpenHelper(context);
        db = helper.getWritableDatabase();
    }


    /* ----------------------------  数据展示操作  ----------------------------------------------- */

    //获得某一年某一月的数据
    @SuppressLint("Range")
    public static List<AccountBean> getdetailsByYearAndMonth(int year, int month){
        System.out.println("Here");
        System.out.println(year);
        System.out.println(month);
        return DBUtils.select("SELECT * FROM account WHERE year = " + year + " AND month = " + month + " ORDER BY id DESC", AccountBean.class);
    }

    //获得某一年某一月的支出或者收入数据
    @SuppressLint("Range")
    public static List<AccountBean>getdetailsByYearAndMonthAndIn_out(int year, int month,String in_out){
        return DBUtils.select("SELECT * FROM account WHERE year = " + year + " AND month = " + month + " AND in_out = '" + in_out + "' ORDER BY money DESC", AccountBean.class);
    }

    //某一天的支出或者收入总额
    @SuppressLint("Range")
       public static float getSumMoneyOneDay(int year,int month,int day,String in_out){
//        System.out.println("GetSumMoney");
//        System.out.println(year);
//        System.out.println(month);
//        System.out.println(day);
//        System.out.println(in_out);
        List<AccountBean> list = DBUtils.select("SELECT * FROM account WHERE year = " + year + " AND month = " + month + " AND day = " + day + " AND in_out = '" + in_out + "'", AccountBean.class);
        float total = 0.0f;
        for (AccountBean bean : list){
            total += bean.getMoney();
        }
        return total;
    }

    //获得某一年某一月的支出或者收入总额
    @SuppressLint("Range")
    public static float getSumMoneyOneMonth(int year,int month,String in_out){
        List<AccountBean> list = DBUtils.select("SELECT * FROM account WHERE year = " + year + " AND month = " + month + " AND in_out = '" + in_out + "'",AccountBean.class);
        float total = 0.0f;
        for (AccountBean bean : list){
            total += bean.getMoney();
        }
        return total;
    }

    //获得某一年的支出或者收入总额
    @SuppressLint("Range")
    public static float getSumMoneyOneYear(int year,String in_out){
        List<AccountBean> list = DBUtils.select("SELECT * FROM account WHERE year = " + year + " AND in_out = '" + in_out + "'",AccountBean.class);
        float total = 0.0f;
        for (AccountBean bean : list){
            total += bean.getMoney();
        }
        return total;
    }

    //获得某个月中某一天最大的支出或者收入的最大金额
    @SuppressLint("Range")
    public static float getMaxMoneyOneDayInMonth(int year,int month,String in_out){
        List<AccountBean> list = DBUtils.select("SELECT * FROM account WHERE year = " + year + " AND month = " + month + " AND in_out = '" + in_out + "' ORDER BY money DESC",AccountBean.class);
        return list.get(0).getMoney();
    }

    //获取某个月中支出/消费各类别的总额
    @SuppressLint("Range")
    public static float getMoneyBytype(int year,int month,String in_out,String type){
        List<AccountBean> list = DBUtils.select("SELECT * FROM account WHERE year = " + year + " AND month = " + month + " AND type = '" + type + "' AND in_out = '" + in_out + "'", AccountBean.class);
        float total = 0.0f;
        for (AccountBean bean : list){
            total += bean.getMoney();
        }
        return total;
    }

}
